package dictcn.com.control;

import java.util.ArrayList;
import java.util.List;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import dictcn.com.model.Book;
import dictcn.com.model.BookName;
import dictcn.com.model.LocalLibrary;

public class MytabOperate {//数据库操作类
	
	private SQLiteDatabase db=null;
	//数据表
	private static final String TABLE_LOCALLIBRARY ="locallibrary";//本地单词表
	private static final String TABLE_BOOKNAME="bookname";//单词本表
	private static final String TABLE_BOOK ="book";//单词本中的单词表

	public MytabOperate(SQLiteDatabase db) {
		this.db=db;
	}
	
	
	//通过单词名或者单词ID号查找本地单词，返回本地单词详细信息
	public LocalLibrary findLocalLibrary(String letter_name){
		
		LocalLibrary localLibrary=new LocalLibrary();
		String sql="SELECT * FROM "+TABLE_LOCALLIBRARY+" WHERE letter_name = ? OR l_id = ?";
		String args[]=new String[]{letter_name,letter_name};
		Cursor result=this.db.rawQuery(sql, args);
		
		if(!result.moveToFirst())//单词不存在
			localLibrary.setL_id(-1);
		else{//单词存在
			for(result.moveToFirst();!result.isAfterLast();result.moveToNext()){
				localLibrary.setL_id(result.getInt(0));
				localLibrary.setLetter_name(result.getString(1));
				localLibrary.setInterpret(result.getString(2));
				localLibrary.setFanyi_en(result.getString(3));
				localLibrary.setFanyi_am(result.getString(4));
				localLibrary.setSign(result.getInt(5));
				localLibrary.setPingyin(result.getString(6));
			}
		}
		this.db.close();
		return localLibrary;
	}
	
	
	//通过生词本名查找生词本，返回生词本详细信息
	public boolean SelctBookName(String b_name){
		boolean flag=false;
		String sql="select * from "+TABLE_BOOKNAME+" where b_name = ?";
		String args[]=new String[]{b_name};
		Cursor result=this.db.rawQuery(sql, args);
		flag=result.moveToFirst();
		this.db.close();
		return flag;
	}
	
	//通过生词本名，创建生词本，返回创建成功与否
	public boolean createBookName(String b_name){
		
		boolean flag=false;
		String sql="select b_name from "+TABLE_BOOKNAME+" where b_name = ?";
		String args[]=new String[]{b_name};
		Cursor result=this.db.rawQuery(sql, args);
		if(!result.moveToFirst())//生词本名不存在，可以创建
		{
			String sql1="INSERT INTO "+TABLE_BOOKNAME+"(b_name,count) VALUES(?,?)";
			String args1[]=new String[]{b_name,"0"};
			this.db.execSQL(sql1, args1);
			flag=true;
		}else{//生词本已经存在
			flag=false;
		}
		this.db.close();
		return flag;
	}
	
	//创建生词本中生词
	public void createBook(int b_id,int l_id,String t_time,int xingji){
		
		String sql="INSERT INTO "+TABLE_BOOK+"(b_id,l_id,t_time,xingji) VALUES(?,?,?,?)";
		Object args[]=new Object[]{b_id,l_id,t_time,xingji};
		this.db.execSQL(sql, args);
		String sql2="UPDATE "+TABLE_BOOKNAME+" SET count=count+1 WHERE b_id ="+b_id;
		this.db.execSQL(sql2);
		this.db.close();
	}
	
	
	//通过单词id号查找，返回本地单词详细信息
	public LocalLibrary selectBook(String b_id){
		LocalLibrary localLibrary=new LocalLibrary();
		String sql="SELECT * FROM "+TABLE_LOCALLIBRARY+" WHERE　b_id=?";
		String args[]=new String[]{b_id};
		Cursor result=this.db.rawQuery(sql, args);
		for(result.moveToFirst();!result.isAfterLast();result.moveToNext()){
			localLibrary.setL_id(result.getInt(0));
			localLibrary.setLetter_name(result.getString(1));
			localLibrary.setInterpret(result.getString(2));
			localLibrary.setFanyi_en(result.getString(3));
			localLibrary.setFanyi_am(result.getString(4));
			localLibrary.setSign(result.getInt(5));
			localLibrary.setPingyin(result.getString(6));
		}
		this.db.close();
		return localLibrary;
	}
	
	//通过生词本名，获取生词本的ID号
	public String GetIdFromBookName(String b_name){
		String resultStr=null;
		String sql="SELECT b_id,b_name FROM "+TABLE_BOOKNAME+" WHERE　b_name=?";
		String args[]=new String[]{b_name};
		Cursor result=this.db.rawQuery(sql, args);

		if(result.moveToFirst())
			resultStr=result.getInt(0)+"";
		else
			resultStr="-1";
		this.db.close();
		return resultStr.trim();
	}
	
	//查找所有生词本的信息，以List集合返回
	public List<BookName> SelctAllBookName(){
		List<BookName> list=new ArrayList<BookName>();
		BookName bookName;
		String sql="SELECT * FROM "+TABLE_BOOKNAME;
		Cursor result=this.db.rawQuery(sql, null);
		for(result.moveToFirst();!result.isAfterLast();result.moveToNext()){
			bookName=new BookName();
			bookName.setB_id(result.getInt(0));
			bookName.setB_name(result.getString(1));
			bookName.setCount(result.getInt(2));
			list.add(bookName);
		}
		this.db.close();
		return list;
	}
	
	//通过生词本id查找，返回生词本中所有生词的基本信息
	public List<Book> SelctAllBook(String b_id){
		List<Book> list=new ArrayList<Book>();
		Book book;
		String sql="SELECT * from "+TABLE_BOOK+" where b_id ="+b_id;
		Cursor result=this.db.rawQuery(sql, null);
		for(result.moveToFirst();!result.isAfterLast();result.moveToNext()){
			book=new Book();
			book.setMbid(result.getInt(0));
			book.setB_id(result.getInt(1));
			book.setL_id(result.getInt(2));
			book.setT_time(result.getString(3));
			book.setXingji(result.getInt(4));
			list.add(book);
		}
		this.db.close();
		return list;
	}
	
	//通过生词名删除生词本
	public void deleteBookNameFromBookName(String b_name){
		String sql="DELETE FROM "+TABLE_BOOKNAME+" WHERE b_name =?";
		String args[]=new String[]{b_name};
		this.db.execSQL(sql, args);
		this.db.close();
	}
	
	//当向生词本中添加单词时的更新操作
	public boolean UpdateBookNameFromBookName(String b_name,String b_new_name){
		boolean flag=false;
		String sql="SELECT * FROM "+TABLE_BOOKNAME+" WHERE b_name=?";
		String args[]=new String[]{b_name};
		Cursor result=this.db.rawQuery(sql, args);
		if(result.moveToFirst())
		{
			String sql2="update "+TABLE_BOOKNAME+" set b_name=? where b_id=?";
			Object args2[]=new Object[]{b_new_name,result.getInt(0)};
			this.db.execSQL(sql2, args2);
			flag=true;
		}else{
			flag=false;
		}
		this.db.close();
		return flag;
	}
}
